package com.jrmf.redenvelopes.order;

import com.jrmf.redenvelopes.order.dao.AccountThirdXfMapper;
import com.jrmf.redenvelopes.order.model.AccountThirdXf;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.util.SAXHelper;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

import javax.annotation.Resource;
import javax.xml.parsers.ParserConfigurationException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
public class ExcelParser {
    @Resource
    private AccountThirdXfMapper acountThirdXfMapper;

    private String filename;
    private SheetContentsHandler handler;

    public ExcelParser(String filename){
        this.filename = filename;
    }

    public ExcelParser setHandler(SheetContentsHandler handler) {
        this.handler = handler;
        return this;
    }

    public void parse(){
        OPCPackage pkg = null;
        InputStream sheetInputStream = null;

        try {
            pkg = OPCPackage.open(filename, PackageAccess.READ);
            XSSFReader xssfReader = new XSSFReader(pkg);

            StylesTable styles = xssfReader.getStylesTable();
            ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
            sheetInputStream = xssfReader.getSheetsData().next();

            processSheet(styles, strings, sheetInputStream);
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage(), e);
        }finally {
            if(sheetInputStream != null){
                try {
                    sheetInputStream.close();
                } catch (IOException e) {
                    throw new RuntimeException(e.getMessage(), e);
                }
            }
            if(pkg != null){
                try {
                    pkg.close();
                } catch (IOException e) {
                    throw new RuntimeException(e.getMessage(), e);
                }
            }
        }
    }

    private void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream) throws SAXException, ParserConfigurationException, IOException{
        XMLReader sheetParser = SAXHelper.newXMLReader();

        if(handler != null){
            sheetParser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, handler, false));
        }else{
            sheetParser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, new SimpleSheetContentsHandler(), false));
        }

        sheetParser.parse(new InputSource(sheetInputStream));
    }

    public static class SimpleSheetContentsHandler implements SheetContentsHandler{
        protected LinkedList<String> row = new LinkedList<>();

        @Override
        public void startRow(int rowNum) {
            row.clear();
        }

        @Override
        public void endRow(int rowNum) {
            System.err.println(rowNum + " : " + row);
        }

        @Override
        public void cell(String cellReference, String formattedValue, XSSFComment comment) {
            row.add(formattedValue);
        }

        @Override
        public void headerFooter(String text, boolean isHeader, String tagName) {

        }
    }
    @Test
    public  void test() {
        long start = System.currentTimeMillis();

        //final List<List<String>> table = new ArrayList<>();
        final List<AccountThirdXf> xfList =new ArrayList<>();
        new ExcelParser("C:\\develop\\xfpay422.xlsx").setHandler(new SimpleSheetContentsHandler(){

            private List<String> fields;

            @Override
            public void endRow(int rowNum) {
                if(rowNum == 0){
                    // 第一行中文描述忽略
                }
                /*else if(rowNum == 1){
                    // 第二行字段名
                    //fields = row;
                    System.out.println(row.pop());
                    System.out.println(row.pop());
                    System.out.println(row.pop());
                    System.out.println(row.pop());
                    System.out.println(row.pop());
                    System.out.println(row.pop());
                    System.out.println(row.pop());
                    System.out.println(row.pop());
                    System.out.println(row.pop());
                }*/
                else {
                    // 数据
                    //table.add(row);
                    //遍历row封装Account
                    AccountThirdXf xf =new AccountThirdXf();
                    if(row.size()==9){
                        xf.setCertNo(row.pop());
                        xf.setTradeTime(row.pop());
                        xf.setMchId(row.pop());
                        xf.setTradeNo(row.pop());
                        xf.setPayIn(Double.valueOf(row.pop()));
                        xf.setPayOut(Double.valueOf(row.pop()));
                        xf.setTotalLeft(Double.valueOf(row.pop()));
                        if("充值".equals(row.pop())){
                            xf.setType((short) 1);
                        }
                        else if("转出".equals(row.pop())){
                            xf.setType((short)2);
                        }
                        else{
                            xf.setType((short)3);
                        }
                    }
                    else{
                        System.out.println("非九列数据："+row);
                    }
                    xfList.add(xf);
                }
            }
        }).parse();

        long end = System.currentTimeMillis();
        int totalNum =xfList.size();
        System.err.println("一共"+totalNum+"条数据");
        //System.err.println(table.get(0).toString());
        System.err.println("解析excel花费:"+(end - start)+"ms");

        //
        int pageSize =0;
        if(totalNum%1024==0){
            pageSize=totalNum/2014;
        }
        else{
            pageSize=(totalNum/1024)+1;
        }

        for(int i=1;i<=pageSize;i++){
            //创建新的
            if(i==pageSize){
                //从 (i-1)*1024--totalNum
                List<AccountThirdXf> subList= xfList.subList((i-1)*1024,totalNum);
                //System.out.println(subList);
                acountThirdXfMapper.batchInsert(subList);

            }
            else{
                //从(i-1)*1024--i*1024
                List<AccountThirdXf> subList= xfList.subList((i-1)*1024,1024*(i));
                acountThirdXfMapper.batchInsert(subList);
            }
        }




    }
}
